﻿using System;
using filmquiz.Src.Domain;
using MySql.Data.MySqlClient;

namespace filmquiz.Src.DatabaseAccess
{

    public class QuizHelper
    {
        private DbAcces dbAccess;
        private static QuizHelper instance = null;

        private QuizHelper()
        {
            dbAccess = DbAcces.getInstance();
        }

        public static QuizHelper getInstance()
        {
            if (instance == null)
            {
                instance = new QuizHelper();
            }
            return instance;
        }

        public bool checkAnswer(int question, string answer)
        {
            String query = "SELECT * FROM questions WHERE question = " + question + " AND answer = '" + answer + "' and round = " + Statics.getInstance().getCurrentRound() + ";";

            MySqlDataReader reader = dbAccess.Select(query);

            bool answerResult = reader.HasRows;

            reader.Close();
            dbAccess.CloseConnection();

            return answerResult;
        }

        public String getAnswer(int question)
        {
            string query = "SELECT * FROM questions WHERE question = " + question + " and round = " + Statics.getInstance().getCurrentRound() + " order by questionsID;";
            MySqlDataReader reader = dbAccess.Select(query);

            reader.Read();

            String answer = reader.GetString(2);

            reader.Close();
            dbAccess.CloseConnection();

            return answer;
        }

        public void setCorrectAnswer(int question, int userId)
        {
            String query = "INSERT INTO answers (question, user, round) VALUES(" + question + ", " + userId + ", " + Statics.getInstance().getCurrentRound() + ");";

            dbAccess.Insert(query);
        }

        public void insertWrongAnswer(String answer, int question, int userId)
        {
            String query = "INSERT INTO wrongAnswers (answer, question, user, round) VALUES ('" + answer + "', " + question + ", " + userId + ", " + Statics.getInstance().getCurrentRound() + ");";
            dbAccess.Insert(query);
        }

        public Round getRoundById(int roundId)
        {
            String query = "SELECT * FROM rounds where roundID = " + roundId + ";";

            MySqlDataReader reader = dbAccess.Select(query);

            Round round = null;

            if (reader.Read())
            {
                round = new Round(reader.GetInt32(0), reader.GetDateTime(1));
            }

            dbAccess.CloseConnection();
            reader.Close();

            return round;
        }

        public bool isRoundOver()
        {
            Round round = getRoundById(Statics.getInstance().getCurrentRound());
            int result = round.end.CompareTo(DateTime.Now);

            return (result < 0);
        }

        public string getFrontpageMessage()
        {
            string query = "select propertyValue from properties where idproperties = 'loggedInMessage';";
            MySqlDataReader reader = dbAccess.Select(query);

            reader.Read();
            string message = reader.GetString(0);
            dbAccess.CloseConnection();
            reader.Close();
           
            return message;
        }

        public string getNotLoggedInMessage()
        {
            string query = "select propertyValue from properties where idproperties = 'notLoggedInMessage';";
            MySqlDataReader reader = dbAccess.Select(query);

            reader.Read();
            string message = reader.GetString(0);
            dbAccess.CloseConnection();
            reader.Close();

            return message;
        }

        public string getAboutText()
        {
            string query = "select propertyValue from properties where idproperties = 'aboutMessage';";
            MySqlDataReader reader = dbAccess.Select(query);

            reader.Read();
            string message = reader.GetString(0);
            dbAccess.CloseConnection();
            reader.Close();

            return message;
        }

        public string getCreateUserText()
        {
            string query = "select propertyValue from properties where idproperties = 'registerMessage';";
            MySqlDataReader reader = dbAccess.Select(query);

            reader.Read();
            string message = reader.GetString(0);
            dbAccess.CloseConnection();
            reader.Close();

            return message;
        }
    }

}